package com.bbs.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.bbs.entity.Article;
import com.bbs.entity.Blocks;
import com.bbs.entity.User;
import com.bbs.util.DBHelp;
import com.bbs.util.DateUtil;
import com.bbs.util.Page;
import com.bbs.util.RowMapper;

public class ArticleDao {
	private DBHelp<Article> db = new DBHelp<Article>();
	
	public int update(Article article) {
		String sql = "update t_article SET NAME = ?, content = ?, t_user_id = ?, state = ?, t_blocks_id = ?, clicknum = ?, TYPE = ? where id = ?";
		
		return db.update(sql, article.getName(), article.getContent(), article.getUser().getId(), article.isState(), article.getBlocks().getId(), article.getClickNum(), article.getType(), article.getId());
	}
	
	public Article findById(int id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id, a.NAME, a.content, a.createdate, a.t_user_id AS userid, a.state,a.settoptime,");
		sql.append("a.t_blocks_id AS blocksid, a.clicknum,a.TYPE,");
		sql.append("b.id AS blocksid, b.NAME AS blocksname, b.pid, b.content AS blockscontent,");
		sql.append("u.id AS userid, u.username, u.userpwd, u.score, u.LEVEL, u.headpic, ");
		sql.append("u.createdate AS usercreatedate, u.email, u.role, u.state AS userstate, u.active, COUNT(r.id) AS replynum ");
		sql.append("FROM t_article AS a LEFT JOIN t_blocks AS b ON b.id = a.t_blocks_id ");
		sql.append("LEFT JOIN t_reply AS r ON a.id = r.t_article_id ");
		sql.append("LEFT JOIN t_user AS u ON u.id = a.t_user_id ");
		sql.append("where a.id = ? GROUP BY (r.id) ");
		
		return db.queryForObject(sql.toString(), new ArticleMapper(), id);
	}
	
	public Article findNewArticle(int blocksid) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id, a.name, a.content, a.createdate, a.t_user_id AS userid, a.state,a.settoptime,");
		sql.append("a.t_blocks_id AS blocksid, a.clicknum,a.TYPE,");
		sql.append("b.id AS blocksid, b.name AS blocksname, b.pid, b.content AS blockscontent,");
		sql.append("u.id AS userid, u.username, u.userpwd, u.score, u.level, u.headpic, ");
		sql.append("u.createdate AS usercreatedate, u.email, u.role, u.state AS userstate, u.active, COUNT(r.id) AS replynum  ");
		sql.append("FROM t_article AS a LEFT JOIN t_blocks AS b ON b.id = a.t_blocks_id ");
		sql.append("LEFT JOIN t_user AS u ON u.id = a.t_user_id ");
		sql.append("LEFT JOIN t_reply AS r ON a.id = r.t_article_id ");
		sql.append("where a.type = 1 AND a.id = (SELECT MAX(id) FROM t_article WHERE t_blocks_id = ?) ");
		sql.append("GROUP BY a.id ");
		
		return db.queryForObject(sql.toString(), new ArticleMapper(), blocksid);
	}
	
	public int save(Article a){
		String sql = "INSERT INTO t_article(NAME,content,createdate,t_user_id,t_blocks_id,settoptime) VALUES (?,?,?,?,?,?)";
		return db.update(sql,  a.getName(),a.getContent(),a.getCreatedate(),a.getUser().getId(),a.getBlocks().getId(),a.getSettoptime());
	}

	public List<Article> findByBid(int id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id,a.name, a.content,a.createdate,a.clicknum,a.type,a.state,a.settoptime,a.t_user_id,t_blocks_id,");
		sql.append("u.id AS userid,u.username,u.userpwd,u.score,u.level,u.headpic,u.createdate AS usercreatedate,u.email,u.role,u.state AS userstate,u.active , ");
		sql.append("b.id AS blocksid,b.name AS blocksname,b.pid,b.content AS blockscontent, COUNT(r.id) AS replynum ");
		sql.append("FROM t_article AS a LEFT JOIN t_user AS u ON t_user_id = u.id ");
		sql.append("LEFT JOIN t_blocks AS b ON t_blocks_id = b.id ");
		sql.append("LEFT JOIN t_reply AS r ON a.id = r.t_article_id ");
		sql.append("where t_blocks_id = ? ");
		sql.append("GROUP BY a.id ");
		sql.append("ORDER BY a.id DESC ");
		return db.queryForList(sql.toString(),new ArticleMapper() , id);
	}

	public List<Article> findByBidAndPage(int id, Page page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id,a.name, a.content,a.createdate,a.clicknum,a.type,a.state,a.t_user_id,t_blocks_id,a.settoptime,");
		sql.append("u.id AS userid,u.username,u.userpwd,u.score,u.level,u.headpic,u.createdate AS usercreatedate,u.email,u.role,u.state AS userstate,u.active , ");
		sql.append("b.id AS blocksid,b.name AS blocksname,b.pid,b.content AS blockscontent, COUNT(r.id) AS replynum ");
		sql.append("FROM t_article AS a LEFT JOIN t_user AS u ON t_user_id = u.id ");
		sql.append("LEFT JOIN t_blocks AS b ON t_blocks_id = b.id ");
		sql.append("LEFT JOIN t_reply AS r ON a.id = r.t_article_id ");
		sql.append("where t_blocks_id = ? ");
		sql.append("GROUP BY a.id ");
		sql.append("ORDER BY a.type desc, a.id DESC ");
		sql.append("limit ?, ?");
		return db.queryForList(sql.toString(),new ArticleMapper() , id, page.getStartNum(), page.getPageSize());
	}

	public void deleteById(int id) {
		String sql = "DELETE FROM t_article WHERE id = ?";
		db.update(sql, id);
	}

	public int getMyCount(User u) {
		String sql = "SELECT COUNT(*) FROM t_article WHERE t_user_id = ?";
		return db.queryForCount(sql, u.getId());
	}

	public List<Article> findMineByUid(int id,Page p) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id,a.name,a.content,a.createdate,a.state,a.clicknum,a.type,a.settoptime,");
		sql.append("t_user_id,t_blocks_id,u.id AS userid,u.username,u.userpwd,u.score,u.level,u.headpic,");
		sql.append("u.createdate AS usercreatedate,u.email,u.role,u.state AS userstate,u.active,");
		sql.append("b.id AS blocksid,b.name AS blocksname,b.pid,b.content AS blockscontent FROM t_article  ");
		sql.append("AS a LEFT JOIN t_user AS u ON t_user_id = u.id LEFT JOIN t_blocks AS b ");
		sql.append("ON t_blocks_id = b.id WHERE t_user_id = ?  order by(a.createdate) desc limit ?,?");
		return db.queryForList(sql.toString(), new ArticleMapper(), id,p.getStartNum(),p.getPageSize());
	}

	public List<Article> searchList(String scontent, Page page) {
		String content = '%' + scontent + '%';
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id,a.name, a.content,a.createdate,a.clicknum,a.type,a.state,a.settoptime,a.t_user_id,t_blocks_id, ");
		sql.append("u.id AS userid,u.username,u.userpwd,u.score,u.level,u.headpic,u.createdate AS usercreatedate,u.email,u.role,u.state AS userstate,u.active , ");
		sql.append("b.id AS blocksid,b.name AS blocksname,b.pid,b.content AS blockscontent ");
		sql.append("FROM t_article AS a LEFT JOIN t_user AS u ON t_user_id = u.id ");
		sql.append("LEFT JOIN t_blocks AS b ON t_blocks_id = b.id ");
		sql.append("where a.name like ? or a.content like ? ");
		sql.append("order BY a.id desc ");
		sql.append("limit ?, ? ");
		return db.queryForList(sql.toString(), new ArticleMapper(), content, content, page.getStartNum(), page.getPageSize());
	}
	
	public int searchCount(String scontent) {
		String content = '%' + scontent + '%';
		StringBuilder sql = new StringBuilder();
		sql.append("select count(a.id) ");
		sql.append("FROM t_article AS a LEFT JOIN t_user AS u ON t_user_id = u.id ");
		sql.append("LEFT JOIN t_blocks AS b ON t_blocks_id = b.id ");
		sql.append("where a.name like ? or a.content like ? ");
		return db.queryForCount(sql.toString(), content, content);
	}

	public List<Article> findAnc() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.id,a.name, a.content,a.createdate,a.clicknum,a.type,a.state,a.settoptime,a.t_user_id,t_blocks_id,");
		sql.append("u.id AS userid,u.username,u.userpwd,u.score,u.level,u.headpic,u.createdate AS usercreatedate,u.email,u.role,u.state AS userstate,u.active , ");
		sql.append("b.id AS blocksid,b.name AS blocksname,b.pid,b.content AS blockscontent, COUNT(r.id) AS replynum ");
		sql.append("FROM t_article AS a LEFT JOIN t_user AS u ON t_user_id = u.id ");
		sql.append("LEFT JOIN t_blocks AS b ON t_blocks_id = b.id ");
		sql.append("LEFT JOIN t_reply AS r ON a.id = r.t_article_id ");
		sql.append("where a.type = 4 ");
		sql.append("GROUP BY a.id ");
		sql.append("ORDER BY a.id DESC ");
		return db.queryForList(sql.toString(),new ArticleMapper());
	}
}


class ArticleMapper implements RowMapper<Article> {

	public Article maprow(ResultSet rs) throws SQLException {
		Article a = new Article();
		a.setId(rs.getInt("id"));
		a.setName(rs.getString("name"));
		a.setContent(rs.getString("content"));
		//String date = DateUtil.friendlyTime(DateUtil.stringToDate(rs.getString("createdate")));
		a.setCreatedate(DateUtil.friendlyTime(DateUtil.stringToDate(rs.getString("createdate"))));
		a.setState(rs.getBoolean("state"));
		a.setClickNum(rs.getInt("clicknum"));
		a.setType(rs.getInt("type"));
		//a.setReplyNum(rs.getInt("replynum"));
		a.setSettoptime(rs.getString("settoptime"));
		
		User u = new User();
		u.setId(rs.getInt("userid"));
		u.setUsername(rs.getString("username"));
		u.setUserpwd(rs.getString("userpwd"));
		u.setScore(rs.getInt("score"));
		u.setEmail(rs.getString("email"));
		u.setCreatedate(rs.getString("usercreatedate"));
		u.setHeadpic(rs.getString("headpic"));
		u.setLevel(rs.getInt("level"));
		u.setActive(rs.getInt("active"));
		u.setRole(rs.getInt("role"));
		u.setState(rs.getBoolean("userstate"));
		
		Blocks b = new Blocks();
		b.setId(rs.getInt("blocksid"));
		b.setName(rs.getString("blocksname"));
		b.setContent(rs.getString("blockscontent"));
		b.setPid(rs.getInt("pid"));
		
		a.setUser(u);
		a.setBlocks(b);
		
		return a;
	}
	
}